SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[updateActionalableMessage] AS 
BEGIN  begin try truncate table actionablealertsmessage; end try begin catch print 'Error in truncate the Table'; end catch  
DECLARE @statusidActive int; 
DECLARE @statusidPending int ;
DECLARE @statusidOver int;
DECLARE @projectTypeIdPartener int; 
Declare @partnername varchar(255); 
Declare @dudate datetime; 
DECLARE @projectid int; 
DECLARE @id int; 
DECLARE @uid1 int; 
DECLARE @uid2 int; 
DECLARE @uid3 int; 
DECLARE @regionid int;  
DECLARE @sprojectid varchar(50); 
DECLARE @message varchar(500); 
DECLARE @displaydate datetime;
 
SELECT @statusidActive=ID from projectstatus where statusname='Active'; 
SELECT @statusidPending=ID from projectstatus where statusname='Pending';
SELECT @statusidOver =ID from projectstatus where statusname='Over'; 
SELECT @projectTypeIdPartener=ID from [projecttype] where [projecttype]='Partner';


--AASS Past Due, test= true; ~PM_DB22
DECLARE AA1_cur cursor for 
SELECT project.id,project.projectid,audits.faselectionmemodue,project.programofficer,project.regionaldirector, project.programassistant,project.regionid FROM 
(project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) 
INNER JOIN audits ON project.id = audits.projectid 
WHERE 
(((projecttype.projecttype)='Partner')
--AND ((audits.faselectionmemodue) Is Not Null And (audits.faselectionmemodue)<getDate()) 
AND ((audits.faselectionmemodue) Is Not Null And datediff(day,(audits.faselectionmemodue),getDate())>0) 
AND ((audits.faselectionmemoreceived) Is Null)
AND ((project.projectstatusid) in (@statusidActive,@statusidPending,@statusidOver)));  
open AA1_cur 
fetch next from AA1_cur into @projectid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; 
while @@FETCH_STATUS=0 
begin 
select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid); 
set @message='The AASS from Partner '+@partnername+' for Grant # '+@sprojectid+' was due on '+convert(varchar,@displaydate,1)+' but has not been received.'; 
insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) values(1,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid);  fetch next from AA1_cur into @projectid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; END; close AA1_cur; deallocate AA1_cur;  

--Final Audit Past Due ~PM_DB27,
DECLARE AA2_cur  cursor for 
SELECT project.id,project.projectid,audits.fadue,project.programofficer,project.regionaldirector, project.programassistant,project.regionid 
FROM 
(project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) 
INNER JOIN audits ON project.id = audits.projectid 
WHERE 
(((projecttype.projecttype)='Partner') 
--AND ((audits.fadue)<getDate())
AND (datediff(day,(audits.fadue),getDate())>0)
AND 
((audits.fareceived) Is Null)
AND ((project.projectstatusid) in (@statusidActive,@statusidPending,@statusidOver)));   
open AA2_cur fetch next from AA2_cur into @projectid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; while @@FETCH_STATUS=0 begin select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid); select @dudate=fadue from audits where projectid=@projectid; 
set @message='The Final Audit from Partner '+@partnername+' for Grant #'+@sprojectid+' was due on '+convert(varchar,@dudate,1); 
insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) values(2,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid); fetch next from AA2_cur into @projectid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid END; close AA2_cur; deallocate AA2_cur;  

--Financial Report Past Due ~PM_DB18,
DECLARE AA3_cur  cursor 
for SELECT projectreports.id,project.id,project.projectid,projectreports.reportdue,project.programofficer,project.regionaldirector, project.programassistant,project.regionid 
FROM (projectreports INNER JOIN project ON projectreports.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id 
WHERE (((projectreports.type)='Financial') 
--AND ((projectreports.reportdue)<getDate()) 
AND (datediff(day,(projectreports.reportdue),getDate())>0) 
AND ((projecttype.projecttype)='Partner') 
AND ((projectreports.reportreceived) Is Null)
AND ((project.projectstatusid) in (@statusidActive,@statusidPending,@statusidOver)));  
Declare @otherid int; 
open AA3_cur fetch next from AA3_cur into @id,@otherid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; while @@FETCH_STATUS=0 begin select @projectid=projectid from projectreports where id=@id; select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid); select @dudate=reportdue from projectreports where id=@id; 
set @message='A Financial Report from Partner '+@partnername+' for Grant # '+@sprojectid+' was due on '+convert(varchar,@dudate,1)+' but has not been received'; 
insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) values(3,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@otherid as varchar(20)),@uid1,@uid2,@uid3,@regionid); fetch next from AA3_cur into @id,@otherid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; END; close AA3_cur; deallocate AA3_cur;  

--Interim Audit Past Due,~PM_DB26, tested
DECLARE AA4_cur  cursor for 
SELECT project.id,project.projectid,audits.iadue,project.programofficer,project.regionaldirector, project.programassistant,project.regionid FROM
(project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) 
INNER JOIN audits ON project.id = audits.projectid WHERE 
(((projecttype.projecttype)='Partner') 
--AND ((audits.iadue)<getDate()) AND ((audits.iareceived) Is Null)
AND (datediff(day,(audits.iadue),getDate())>0) AND ((audits.iareceived) Is Null)
AND ((project.projectstatusid) in (@statusidActive,@statusidPending,@statusidOver)));  
open AA4_cur fetch next from AA4_cur into @projectid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; while @@FETCH_STATUS=0 begin select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid); select @dudate=iadue from audits where projectid=@projectid;
set @message='An Interim Audit from Partner '+@partnername+' for Grant #'+@sprojectid+' was due on '+convert(varchar,@dudate,1)+' but has not been received'; 
insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) values(4,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid); fetch next from AA4_cur into @projectid ,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; END; close AA4_cur; deallocate AA4_cur;  

--Narrative Report Past Due ~PM_DB17
DECLARE AA5_cur  cursor for 
SELECT projectreports. id,project.id,project.projectid,projectreports.reportdue,project.programofficer,project.regionaldirector, project.programassistant,project.regionid 
FROM 
(projectreports INNER JOIN project ON projectreports.projectid = project.id) 
INNER JOIN projecttype ON project.projecttypeid = projecttype.id 
--WHERE (((projectreports.type)='Narrative') AND ((projectreports.reportdue)<getDate()) 
WHERE (((projectreports.type)='Narrative') AND (datediff(day,(projectreports.reportdue),getDate())>0) 
AND ((projecttype.projecttype)='Partner') AND ((projectreports.reportreceived) Is Null)
AND ((project.projectstatusid) in (@statusidActive,@statusidPending,@statusidOver)));  
open AA5_cur fetch next from AA5_cur into @id,@otherid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; while @@FETCH_STATUS=0 begin select @projectid=projectid from projectreports where id=@id; select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid); select @dudate=reportdue from projectreports where id=@id; 
set @message='A Narrative Report from Partner '+@partnername+' for Grant # '+@sprojectid+' was due on '+convert(varchar,@dudate,1)+' but has not been received'; 
insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) values(5,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@otherid as varchar(20)),@uid1,@uid2,@uid3,@regionid); fetch next from AA5_cur into @id,@otherid,@sprojectid ,@displaydate,@uid1,@uid2,@uid3,@regionid; END; close AA5_cur; deallocate AA5_cur; 

--IMP Past Due ~PM_DB20
DECLARE AA6_cur  cursor for 
SELECT imp.id,project.id,project.projectid,imp.impduedate,project.programofficer,project.regionaldirector, project.programassistant,project.regionid FROM
(imp INNER JOIN project ON imp.projectid = project.id) 
INNER JOIN projecttype ON project.projecttypeid = projecttype.id 
WHERE (
--((imp.impduedate)<getDate()) 
(datediff(day,(imp.impduedate),getDate())>0) 
AND ((projecttype.projecttype)='Partner')
AND ((imp.impreceived) Is Null)
AND ((project.projectstatusid) in (@statusidActive,@statusidPending,@statusidOver)));  
open AA6_cur fetch next from AA6_cur into @id,@otherid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; while @@FETCH_STATUS=0 begin select @projectid=projectid from imp where id=@id; select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid); select @dudate=impduedate from imp where id=@id; 
set @message='An IMP from Partner '+@partnername+' for Grant #'+@sprojectid+' was due on '+convert(varchar,@dudate,1)+' but has not been received'; 
insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) values(6,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@otherid as varchar(20)),@uid1,@uid2,@uid3,@regionid); fetch next from AA6_cur into @id,@otherid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; END; close AA6_cur; deallocate AA6_cur; 

--Payment Request has been unpaid for more than 30 days
DECLARE AA7_cur  cursor for 
SELECT paymentrequest.id,paymentrequest.createdate,paymentrequest.createdby 
FROM paymentrequest WHERE 
--(((paymentrequest.createdate)<getDate()-30) AND 
((datediff(day,(paymentrequest.createdate),getDate())>30) AND 
(Not (paymentrequest.statuscode)='paid' And Not (paymentrequest.statuscode)='cancelled'));
 
DECLARE @payeeName varchar(255); DECLARE @payeeid int; 
open AA7_cur fetch next from AA7_cur into @id,@displaydate,@uid1; while @@FETCH_STATUS=0 begin select @payeeid=payeeid from paymentrequest where id=@id; select @payeeName=payeename,@dudate=payee.createdate from payee where id=@payeeid; 
set @message='Payment Request #'+cast(@id as varchar(20))+' created on '+convert(varchar,@displaydate,1)+' for payee '+@payeeName+' has been open and unpaid for over 30 days. '; 
insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant) values(7,@message,@displaydate+30,'showEditPaymentrequest.do?id='+cast(@id as varchar(20)),@uid1,@uid1,@uid1); fetch next from AA7_cur into @id,@displaydate,@uid1 END; close AA7_cur; deallocate AA7_cur; 

--Payment Request Rejected
DECLARE AA8_cur  cursor for select id,paymentrequest.createdby from paymentrequest  where  statuscode='Rejected' ; DECLARE @lastupdate datetime; 
open AA8_cur fetch next from AA8_cur into @id,@uid1; while @@FETCH_STATUS=0 begin select @payeeid=payeeid from paymentrequest where id=@id; select @payeeName=payeename,@dudate=payee.createdate,@lastupdate=lastupdated from payee where id=@payeeid;  
set @message='Payment Request #'+cast(@id as varchar(20))+' created on '+convert(varchar,@dudate,1)+' for payee '+@payeeName+' was rejected on '+convert(varchar,@lastupdate,1); 
insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant) values(8,@message,@lastupdate,'showEditPaymentrequest.do?id='+cast(@id as varchar(20)),@uid1,@uid1,@uid1); fetch next from AA8_cur into @id,@uid1 END; close AA8_cur; deallocate AA8_cur; 


--Partner became active over 60 days ago and no payment
DECLARE AA9_cur  cursor for select id,projectstartdate,projectid,project.programofficer,project.regionaldirector, project.programassistant,project.regionid from project  where  projecttypeid=@projectTypeIdPartener and  projectstatusid=@statusidActive 
--and projectstartdate<GETDATE()-60 and 0=(select count(*) from paymentrequestlineitem where projectid=project.id)  
and datediff(day,projectstartdate,GETDATE())>60 and 0=(select count(*) from paymentrequestlineitem where projectid=project.id)  
open AA9_cur fetch next from AA9_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid; while @@FETCH_STATUS=0 begin select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid); 
 set @message='Grant #'+@sprojectid+' for Partner '+@partnername+ ' became active over 60 days ago on '+convert(varchar,@dudate,1)+ ' and there has been no Payment Request activity against it'; 
insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) values(9,@message,@dudate+60,'showEditPartnergrantView.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid); fetch next from AA9_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid; END; close AA9_cur; deallocate AA9_cur; 

--Project became active over 60 days ago 
DECLARE AA10_cur  cursor for select id,projectstartdate,projectid,project.programofficer,project.regionaldirector, project.programassistant,project.regionid from project  where  projecttypeid!=@projectTypeIdPartener and projectstatusid=@statusidActive and datediff(day,projectstartdate,getdate())>60 and 0=(select count(*) from paymentrequestlineitem where projectid=project.id) 
open AA10_cur fetch next from AA10_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid; while @@FETCH_STATUS=0 begin 
 set @message='Project #'+@sprojectid+ ' became active over 60 days ago on '+convert(varchar,@dudate,1)+ ' and there has been no Payment Request activity against it';  
insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) values(10,@message,@dudate+60,'showCipeprojectSummary.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid); fetch next from AA10_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid; END; close AA10_cur; deallocate AA10_cur;

--The start date of Grant is set for partner but the Signed Singnature 
DECLARE AA11_cur  cursor for SELECT project.id, project.projectstartDate,project.projectid,project.programofficer,project.regionaldirector, project.programassistant,project.regionid FROM (project INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id WHERE (((projecttype.projecttype)='Partner') 
--AND ((project.projectstartDate)<getDate()) 
AND (datediff(day,(project.projectstartDate),getDate())>0) 
AND ((projectstatus.statusname)='pending' Or (projectstatus.statusname)='active') AND ((project.receiptofsignedagreement) Is Null)); 
open AA11_cur fetch next from AA11_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid; while @@FETCH_STATUS=0 begin select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid);  
set @message='The start date of Grant #'+@sprojectid+ ' is set for '+convert(varchar,@dudate,1) + 'for Partner '+@partnername+ ' but the Signed Signature agreement has not been received'; IF @message is not null insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) values(11,@message,@dudate+1,'showEditPartnergrantView.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid); fetch next from AA11_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid; END; close AA11_cur; deallocate AA11_cur;   

--Donor award # is scheduled to expire in less than 90 days.
DECLARE AA12_cur  cursor for select donoraward.donorawardnumber,donoraward.id,donoraward.enddate from (donoraward LEFT JOIN donor ON donoraward.donorawardtypeid = donor.id) LEFT JOIN donorawardstatus ON donoraward.donorawardstatusid = donorawardstatus.id WHERE (((donoraward.enddate) Between getDate() And getDate()+90) AND ((donorawardstatus.statusname)='active')); 
open AA12_cur fetch next from AA12_cur into @sprojectid,@id,@dudate; while @@FETCH_STATUS=0 begin 
set @message='Donor Award # '+@sprojectid+' is scheduled to expire in less than 90 days, on '+convert(varchar,@dudate,1); 
insert into actionablealertsmessage(actionablealertsid,message,date,url) values(12,@message,@dudate-90,'showEditDonoraward.do?id='+cast(@id as varchar(20))); 
fetch next from AA12_cur into @sprojectid,@id,@dudate; 
END; 
close AA12_cur; 
deallocate AA12_cur; 

execute sp_rename 'Cacheactionablealertsmessage' ,'oldCacheactionablealertsmessage'; execute sp_rename 'actionablealertsmessage' ,'Cacheactionablealertsmessage'; execute sp_rename 'oldCacheactionablealertsmessage' ,'actionablealertsmessage'; truncate table actionablealertsmessage;  END;


GO